package cn.hicard.risk.report;

import java.io.ByteArrayOutputStream;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import cn.hicard.risk.common.Constant;
import cn.hicard.risk.entity.BdCallorder;
import cn.hicard.risk.entity.BdTradefileinfo;
import cn.hicard.risk.entity.LimitFreezeinfo;
import cn.hicard.util.CardInfo;
import cn.hicard.util.DateHelp;
import cn.hicard.risk.vo.Poscollectwarning;
import cn.hicard.risk.vo.PoscollectwarningVo;
import cn.hicard.risk.vo.PoscreditoftentimeVo;
import cn.hicard.risk.vo.Poscreditratio;
import cn.hicard.risk.vo.PoscreditratioVo;
import cn.hicard.risk.vo.Postradedeviant;

/**  报表模板工具   */
public class ReportTemplet {
	
	private static Logger logger = Logger.getLogger(Constant.LOGGER_MAIN);
	
	/* 经验：EXCEL相关的处理，由于支持包的限制，EXCEL相关的类不能修改引用型变量的值，引用变量也不能被多次引用
	 * 否则会引读写EXCEL相关的异常
	 * 因此没办法重构代码
	 * 同时，此包需要JDK1.6支持
	 */
	
	
	/**
	 * 通用简单格式报表模板
	 */
	public ByteArrayOutputStream createGeneralTemplet(
			String title, String descrip,
			String[] fields, List<Object[]> source){
		// -------------------构造 可写入数据的EXCEL 结构    ------------------- //
		WritableWorkbook wwbook = null;	
		// 初始化一个20KB的内存空间
		ByteArrayOutputStream os = new ByteArrayOutputStream(20*1024);
		// 列的数量=字段数+序号列
		int clmCount = fields.length + 1;
		try {
			// 创建Excel
			wwbook = Workbook.createWorkbook(os);
			// 创建工作表sheet
			WritableSheet wsheet = wwbook.createSheet("附件1", 0);
						
			// 合并大标题单元格
			wsheet.mergeCells(0, 0, clmCount-1, 0);			
			// 标题居中对齐
			titleFmt.setAlignment(Alignment.CENTRE);
			// 合并简介单元格
			wsheet.mergeCells(0, 1, clmCount-1, 1);
			
			jxl.write.Label titleCell = new Label(0, 0, title,titleFmt); // 标题						
			jxl.write.Label descripCell = new Label(0, 1, descrip);			// 简介
			wsheet.addCell(titleCell);
			wsheet.addCell(descripCell);
			
			jxl.write.Label sequenceCell = new Label(0, 2, "序号");
			wsheet.addCell(sequenceCell);
			for(int i=0; i<fields.length; i++){
				String field = fields[i];
				// 正式数据从第3行开始,列从第2列开始，第一列用于序号
				jxl.write.Label fieldCell = new Label(i+1,2, field);
				wsheet.addCell(fieldCell);
			}


			// -------------------从数据源中读取数据填充单元格    ------------------- //
			int startRow = 3; // 从第4行开
			for (int k=0; k<source.size(); k++) {
				Object[] data = source.get(k);
				wsheet.addCell(new Label(0, startRow, String.valueOf(k+1)));
				for(int i=0; i<data.length; i++){					
					wsheet.addCell(new Label(i+1, startRow, data[i]==null?"":data[i].toString()));
				}				
				startRow++;
			}			
			// 写入
			wwbook.write();
			// 关闭
			wwbook.close();
		} catch (Exception e) {
			logger.error("创建Excel报表模板失败！", e);
		} 		
		return os;
	}
	
	
	//[start]紧急调单相关报表

	// 风控传统POS冻结报表
	public ByteArrayOutputStream createRiskTradeTemplet(List<BdCallorder> source, String title, String startDate, String endDate){
		// -------------------构造 可写入数据的EXCEL 结构    ------------------- //
		WritableWorkbook wwbook = null;	
		// 初始化一个20KB的内存空间
		ByteArrayOutputStream os = new ByteArrayOutputStream(20*1024);
		try {
			// 创建Excel
			wwbook = Workbook.createWorkbook(os);
			// 创建工作表sheet
			WritableSheet wsheet = wwbook.createSheet("附件1", 0);
						
			// 合并大标题单元格
			wsheet.mergeCells(0, 0, 6, 0);
			// 居中对齐
			titleFmt.setAlignment(Alignment.CENTRE);			
			jxl.write.Label c00 = new Label(0, 0, title,titleFmt);						
			jxl.write.Label c01 = new Label(0, 1, "共计" + source.size() + "条数据，报表生成条件：开始日期 "+startDate+"            结束日期 "+endDate);			
			jxl.write.Label c02 = new Label(0, 2, "序号");
			jxl.write.Label c12 = new Label(1, 2, "商户编号");
			jxl.write.Label c22 = new Label(2, 2, "商户名称");
			jxl.write.Label c32 = new Label(3, 2, "订单号");
			jxl.write.Label c42 = new Label(4, 2, "交易日期时间");
			jxl.write.Label c52 = new Label(5, 2, "处理时间");			
			jxl.write.Label c62 = new Label(6, 2, "交易金额");
			
			wsheet.addCell(c00);
			wsheet.addCell(c01);
			wsheet.addCell(c02);			
			wsheet.addCell(c12);
			wsheet.addCell(c22);
			wsheet.addCell(c32);
			wsheet.addCell(c42);
			wsheet.addCell(c52);			
			wsheet.addCell(c62);
			
			// -------------------从数据源中读取数据填充单元格    ------------------- //
			int startRow = 4; // 从第5行开
			for (int i=0; i<source.size(); i++) {
				BdCallorder trade = source.get(i);
				jxl.write.Label cellCol0 = new Label(0, startRow, String.valueOf(i + 1));
				jxl.write.Label cellCol1 = new Label(1, startRow, trade.getMerchno());
				jxl.write.Label cellCol2 = new Label(2, startRow, trade.getMerchname());				
				jxl.write.Label cellCol3 = new Label(3, startRow, trade.getOrderno());			
				jxl.write.Label cellCol4 = new Label(4, startRow, DateHelp.numberToDate(trade.getTransdate() + trade.getTranstime()));
				jxl.write.Label cellCol5 = new Label(5, startRow, trade.getProcdate());
				jxl.write.Label cellCol6 = new Label(6, startRow, trade.getTransmoney());

				wsheet.addCell(cellCol0);
				wsheet.addCell(cellCol1);
				wsheet.addCell(cellCol2);
				wsheet.addCell(cellCol3);
				wsheet.addCell(cellCol4);
				wsheet.addCell(cellCol5);
				wsheet.addCell(cellCol6);
				startRow++;
			}			
			// 写入
			wwbook.write();
			// 关闭
			wwbook.close();
		} catch (Exception e) {
			logger.error("创建Excel报表模板失败！", e);
		} 		
		return os;
	}
	
	// 紧急调单管理报表
	
	//[end]
	
	
	
	
	//[start]传统pos相关报表
	
	//  ---------------  Excel 模板常用   -----------------  //
	/******* 大标题,20号字体*******/
	private WritableFont font20 = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD);
	private WritableCellFormat titleFmt = new WritableCellFormat(font20);
	
	
	//  ---------------  模板的一些基础数据   -----------------  //
	
	/** 一般商户交易数据模板，适用于：
	 * 2.1.传统pos商户的交易报表、2.3.传统pos商户非营业时间交易报表、2.4.传统pos商户大额交易报表、2.6.传统pos商户信用卡大额交易报表
	 * */
	public ByteArrayOutputStream createPostradeTemplet(List<BdTradefileinfo> source, String title, String startDate, String endDate){
		// -------------------构造 可写入数据的EXCEL 结构    ------------------- //
		WritableWorkbook wwbook = null;	
		// 初始化一个20KB的内存空间
		ByteArrayOutputStream os = new ByteArrayOutputStream(20*1024);
		try {
			// 创建Excel
			wwbook = Workbook.createWorkbook(os);
			// 创建工作表sheet
			WritableSheet wsheet = wwbook.createSheet("附件1", 0);
						
			// 合并大标题单元格
			wsheet.mergeCells(0, 0, 10, 0);
			// 居中对齐
			titleFmt.setAlignment(Alignment.CENTRE);			
			jxl.write.Label c00 = new Label(0, 0, title,titleFmt);						
			jxl.write.Label c01 = new Label(0, 1, "报表生成日期：" + DateHelp.dateTime());
			jxl.write.Label c02 = new Label(0, 2, "报表生成条件：开始日期 "+startDate+"            结束日期 "+endDate);			
			jxl.write.Label c03 = new Label(0, 3, "序号");
			jxl.write.Label c13 = new Label(1, 3, "商户编号");
			jxl.write.Label c23 = new Label(2, 3, "商户名称");
			jxl.write.Label c33 = new Label(3, 3, "终端号");
			jxl.write.Label c43 = new Label(4, 3, "地区");
			jxl.write.Label c53 = new Label(5, 3, "交易日期");			
			jxl.write.Label c63 = new Label(6, 3, "交易时间");
			jxl.write.Label c73 = new Label(7, 3, "交易金额");
			jxl.write.Label c83 = new Label(8, 3, "交易卡号");
			jxl.write.Label c93 = new Label(9, 3, "卡属性");
			jxl.write.Label c103 = new Label(10, 3, "系统参考号");
			
			wsheet.addCell(c00);
			wsheet.addCell(c01);
			wsheet.addCell(c02);
			wsheet.addCell(c03);			
			wsheet.addCell(c13);
			wsheet.addCell(c23);
			wsheet.addCell(c33);
			wsheet.addCell(c43);
			wsheet.addCell(c53);			
			wsheet.addCell(c63);
			wsheet.addCell(c73);
			wsheet.addCell(c83);
			wsheet.addCell(c93);
			wsheet.addCell(c103);
						
			// -------------------从数据源中读取数据填充单元格    ------------------- //
			int startRow = 4; // 从第5行开
			for (int i=0; i<source.size(); i++) {
				BdTradefileinfo trade = source.get(i);
				jxl.write.Label cellCol0 = new Label(0, startRow, String.valueOf(i + 1));
				jxl.write.Label cellCol1 = new Label(1, startRow, trade.getMerchno());
				jxl.write.Label cellCol2 = new Label(2, startRow, trade.getMerchname());
				
				jxl.write.Label cellCol3 = new Label(3, startRow, trade.getTermno());
				jxl.write.Label cellCol4 = new Label(4, startRow, trade.getArea());
				jxl.write.Label cellCol5 = new Label(5, startRow, trade.getTransdate());
				jxl.write.Label cellCol6 = new Label(6, startRow, trade.getTranstime());
				jxl.write.Label cellCol7 = new Label(7, startRow, trade.getTransmoney());
				jxl.write.Label cellCol8 = new Label(8, startRow, trade.getCardno());
				//卡属性
				jxl.write.Label cellCol9 = new Label(9, startRow, CardInfo.getCardtypeName(trade.getCardtype()));
				jxl.write.Label cellCol10 = new Label(10, startRow, trade.getSysno());
				wsheet.addCell(cellCol0);
				wsheet.addCell(cellCol1);
				wsheet.addCell(cellCol2);
				wsheet.addCell(cellCol3);
				wsheet.addCell(cellCol4);
				wsheet.addCell(cellCol5);
				wsheet.addCell(cellCol6);
				wsheet.addCell(cellCol7);
				wsheet.addCell(cellCol8);
				wsheet.addCell(cellCol9);
				wsheet.addCell(cellCol10);
				startRow++;
			}			
			// 写入
			wwbook.write();
			// 关闭
			wwbook.close();
		} catch (Exception e) {
			logger.error("创建Excel报表模板失败！", e);
		} 		
		return os;
	}

	/** 传统pos商户信用卡比例报表，适用于：
	 * 2.2.传统pos商户信用卡比例报表
	 * */
	public ByteArrayOutputStream createCreditratioTemplet(PoscreditratioVo creditratio, String title, String startDate, String endDate){
		List<Poscreditratio> source  = creditratio.poscreditratios;
		// -------------------构造 可写入数据的EXCEL 结构    ------------------- //
		WritableWorkbook wwbook = null;	
		// 初始化一个20KB的内存空间，不足自动增加
		ByteArrayOutputStream os = new ByteArrayOutputStream(20*1024);
		try {
			// 创建Excel
			wwbook = Workbook.createWorkbook(os);
			// 创建工作表sheet
			WritableSheet wsheet = wwbook.createSheet("附件1", 0);
						
			// 合并大标题单元格
			wsheet.mergeCells(0, 0, 10, 0);
			// 居中对齐
			titleFmt.setAlignment(Alignment.CENTRE);			
			jxl.write.Label c00 = new Label(0, 0, title,titleFmt);						
			jxl.write.Label c01 = new Label(0, 1, "报表生成日期：" + DateHelp.dateTime());
			jxl.write.Label c02 = new Label(0, 2, "报表生成条件：开始日期 "+startDate+"            结束日期 "+endDate);			
			jxl.write.Label c03 = new Label(0, 3, "序号");
			jxl.write.Label c13 = new Label(1, 3, "商户编号");
			jxl.write.Label c23 = new Label(2, 3, "商户名称");
			jxl.write.Label c33 = new Label(3, 3, "终端号");
			jxl.write.Label c43 = new Label(4, 3, "地区");
			jxl.write.Label c53 = new Label(5, 3, "交易总额");
			jxl.write.Label c63 = new Label(6, 3, "交易笔数");
			jxl.write.Label c73 = new Label(7, 3, "信用卡交易总额");
			jxl.write.Label c83 = new Label(8, 3, "信用卡交易笔数");
			jxl.write.Label c93 = new Label(9, 3, "信用卡交易总额比例");
			jxl.write.Label c103 = new Label(10, 3, "信用卡交易笔数比例");			
			wsheet.addCell(c00);
			wsheet.addCell(c01);
			wsheet.addCell(c02);
			wsheet.addCell(c03);			
			wsheet.addCell(c13);
			wsheet.addCell(c23);
			wsheet.addCell(c33);
			wsheet.addCell(c43);
			wsheet.addCell(c53);			
			wsheet.addCell(c63);
			wsheet.addCell(c73);
			wsheet.addCell(c83);
			wsheet.addCell(c93);
			wsheet.addCell(c103);
						
			// -------------------从数据源中读取数据填充单元格    ------------------- //
			int startRow = 4; // 从第5行开
			for (int i=0; i<source.size(); i++) {
				Poscreditratio trade = source.get(i);
				jxl.write.Label cellCol0 = new Label(0, startRow, String.valueOf(i + 1));
				jxl.write.Label cellCol1 = new Label(1, startRow, trade.getMerchno());
				jxl.write.Label cellCol2 = new Label(2, startRow, trade.getMerchname());
				jxl.write.Label cellCol3 = new Label(3, startRow, trade.getTermno());
				jxl.write.Label cellCol4 = new Label(4, startRow, trade.getArea());
				jxl.write.Label cellCol5 = new Label(5, startRow, trade.getTransmoney());
				jxl.write.Label cellCol6 = new Label(6, startRow, trade.getTranscount());
				jxl.write.Label cellCol7 = new Label(7, startRow, trade.getCredittransmoney());
				jxl.write.Label cellCol8 = new Label(8, startRow, trade.getCredittranscount());
				jxl.write.Label cellCol9 = new Label(9, startRow, trade.getMoneyratio());
				jxl.write.Label cellCol10 = new Label(10, startRow, trade.getCountratio());
				
				wsheet.addCell(cellCol0);
				wsheet.addCell(cellCol1);
				wsheet.addCell(cellCol2);
				wsheet.addCell(cellCol3);
				wsheet.addCell(cellCol4);
				wsheet.addCell(cellCol5);
				wsheet.addCell(cellCol6);
				wsheet.addCell(cellCol7);
				wsheet.addCell(cellCol8);
				wsheet.addCell(cellCol9);
				wsheet.addCell(cellCol10);
				startRow++;
			}			
			// 合计信息
			startRow++;
			jxl.write.Label cell3 = new Label(4, startRow, "合计：");
			jxl.write.Label cell4 = new Label(5, startRow, "￥" + creditratio.totalMoney);
			jxl.write.Label cell5 = new Label(6, startRow, creditratio.totalCount + "笔");
			jxl.write.Label cell6 = new Label(7, startRow, "￥" + creditratio.totalCreditMoney);
			jxl.write.Label cell7 = new Label(8, startRow, creditratio.totalCreditCount + "笔");			
			wsheet.addCell(cell3);
			wsheet.addCell(cell4);
			wsheet.addCell(cell5);
			wsheet.addCell(cell6);
			wsheet.addCell(cell7);
			
			// 写入
			wwbook.write();
			// 关闭
			wwbook.close();
		} catch (Exception e) {			
			logger.error("创建Excel报表模板失败！", e);
		} 		
		return os;
	}


	/** 2.5.传统pos商户信用卡频繁交易报表
	 * 单个与 一般商户交易数据模板一样，只不过有两个sheet，数据源不同而已	 
	 * */
	public ByteArrayOutputStream createPoscreditoftentimeTemplet(PoscreditoftentimeVo vo, String title, String startDate, String endDate){
		// -------------------构造 可写入数据的EXCEL 结构    ------------------- //
		WritableWorkbook wwbook = null;	
		// 初始化一个20KB的内存空间
		ByteArrayOutputStream os = new ByteArrayOutputStream(20*1024);
		try {
			// 创建Excel
			wwbook = Workbook.createWorkbook(os);
			// 创建工作表sheet1
			WritableSheet wsheet1 = wwbook.createSheet("附件1", 0);
			// 创建工作表sheet2
			WritableSheet wsheet2 = wwbook.createSheet("附件2", 0);
			
			// 合并大标题单元格
			wsheet1.mergeCells(0, 0, 10, 0);
			// 居中对齐
			titleFmt.setAlignment(Alignment.CENTRE);			
			jxl.write.Label c00 = new Label(0, 0, title,titleFmt);						
			jxl.write.Label c01 = new Label(0, 1, "报表生成日期：" + DateHelp.dateTime());
			jxl.write.Label c02 = new Label(0, 2, "报表生成条件：开始日期 "+startDate+"            结束日期 "+endDate);			
			jxl.write.Label c03 = new Label(0, 3, "序号");
			jxl.write.Label c13 = new Label(1, 3, "商户编号");
			jxl.write.Label c23 = new Label(2, 3, "商户名称");
			jxl.write.Label c33 = new Label(3, 3, "终端号");
			jxl.write.Label c43 = new Label(4, 3, "地区");
			jxl.write.Label c53 = new Label(5, 3, "交易日期");			
			jxl.write.Label c63 = new Label(6, 3, "交易时间");
			jxl.write.Label c73 = new Label(7, 3, "交易金额");
			jxl.write.Label c83 = new Label(8, 3, "交易卡号");
			jxl.write.Label c93 = new Label(9, 3, "卡属性");
			jxl.write.Label c103 = new Label(10, 3, "系统参考号");
			
			wsheet1.addCell(c00);
			wsheet1.addCell(c01);
			wsheet1.addCell(c02);
			wsheet1.addCell(c03);			
			wsheet1.addCell(c13);
			wsheet1.addCell(c23);
			wsheet1.addCell(c33);
			wsheet1.addCell(c43);
			wsheet1.addCell(c53);			
			wsheet1.addCell(c63);
			wsheet1.addCell(c73);
			wsheet1.addCell(c83);
			wsheet1.addCell(c93);
			wsheet1.addCell(c103);
						
			// -------------------从数据源中读取数据填充单元格    ------------------- //
			int startRow = 4; // 从第5行开
			for (int i=0; i<vo.creditoftentime_2_3000.size(); i++) {
				BdTradefileinfo trade = vo.creditoftentime_2_3000.get(i);
				jxl.write.Label cellCol0 = new Label(0, startRow, String.valueOf(i + 1));
				jxl.write.Label cellCol1 = new Label(1, startRow, trade.getMerchno());
				jxl.write.Label cellCol2 = new Label(2, startRow, trade.getMerchname());
				
				jxl.write.Label cellCol3 = new Label(3, startRow, trade.getTermno());
				jxl.write.Label cellCol4 = new Label(4, startRow, trade.getArea());
				jxl.write.Label cellCol5 = new Label(5, startRow, trade.getTransdate());
				jxl.write.Label cellCol6 = new Label(6, startRow, trade.getTranstime());
				jxl.write.Label cellCol7 = new Label(7, startRow, trade.getTransmoney());
				jxl.write.Label cellCol8 = new Label(8, startRow, trade.getCardno());
				//卡属性
				jxl.write.Label cellCol9 = new Label(9, startRow, CardInfo.getCardtypeName(trade.getCardtype()));
				jxl.write.Label cellCol10 = new Label(10, startRow, trade.getSysno());
				wsheet1.addCell(cellCol0);
				wsheet1.addCell(cellCol1);
				wsheet1.addCell(cellCol2);
				wsheet1.addCell(cellCol3);
				wsheet1.addCell(cellCol4);
				wsheet1.addCell(cellCol5);
				wsheet1.addCell(cellCol6);
				wsheet1.addCell(cellCol7);
				wsheet1.addCell(cellCol8);
				wsheet1.addCell(cellCol9);
				wsheet1.addCell(cellCol10);
				startRow++;
			}	
			
			
			// 合并大标题单元格
			wsheet2.mergeCells(0, 0, 10, 0);
			// 居中对齐
//			titleFmt.setAlignment(Alignment.CENTRE);			
			jxl.write.Label cc00 = new Label(0, 0, title,titleFmt);						
			jxl.write.Label cc01 = new Label(0, 1, "报表生成日期：" + DateHelp.dateTime());
			jxl.write.Label cc02 = new Label(0, 2, "报表生成条件：开始日期 "+startDate+"            结束日期 "+endDate);			
			jxl.write.Label cc03 = new Label(0, 3, "序号");
			jxl.write.Label cc13 = new Label(1, 3, "商户编号");
			jxl.write.Label cc23 = new Label(2, 3, "商户名称");
			jxl.write.Label cc33 = new Label(3, 3, "终端号");
			jxl.write.Label cc43 = new Label(4, 3, "地区");
			jxl.write.Label cc53 = new Label(5, 3, "交易日期");			
			jxl.write.Label cc63 = new Label(6, 3, "交易时间");
			jxl.write.Label cc73 = new Label(7, 3, "交易金额");
			jxl.write.Label cc83 = new Label(8, 3, "交易卡号");
			jxl.write.Label cc93 = new Label(9, 3, "卡属性");
			jxl.write.Label cc103 = new Label(10, 3, "系统参考号");
			
			wsheet2.addCell(cc00);
			wsheet2.addCell(cc01);
			wsheet2.addCell(cc02);
			wsheet2.addCell(cc03);			
			wsheet2.addCell(cc13);
			wsheet2.addCell(cc23);
			wsheet2.addCell(cc33);
			wsheet2.addCell(cc43);
			wsheet2.addCell(cc53);			
			wsheet2.addCell(cc63);
			wsheet2.addCell(cc73);
			wsheet2.addCell(cc83);
			wsheet2.addCell(cc93);
			wsheet2.addCell(cc103);
						
			// -------------------从数据源中读取数据填充单元格    ------------------- //
			startRow = 4; // 从第5行开
			for (int i=0; i<vo.creditoftentime_4_10.size(); i++) {
				BdTradefileinfo trade = vo.creditoftentime_4_10.get(i);
				jxl.write.Label cellCol0 = new Label(0, startRow, String.valueOf(i + 1));
				jxl.write.Label cellCol1 = new Label(1, startRow, trade.getMerchno());
				jxl.write.Label cellCol2 = new Label(2, startRow, trade.getMerchname());
				
				jxl.write.Label cellCol3 = new Label(3, startRow, trade.getTermno());
				//地区
				jxl.write.Label cellCol4 = new Label(4, startRow, trade.getArea());
				jxl.write.Label cellCol5 = new Label(5, startRow, trade.getTransdate());
				jxl.write.Label cellCol6 = new Label(6, startRow, trade.getTranstime());
				jxl.write.Label cellCol7 = new Label(7, startRow, trade.getTransmoney());
				jxl.write.Label cellCol8 = new Label(8, startRow, trade.getCardno());
				//卡属性
				jxl.write.Label cellCol9 = new Label(9, startRow, CardInfo.getCardtypeName(trade.getCardtype()));
				jxl.write.Label cellCol10 = new Label(10, startRow, trade.getSysno());
				wsheet2.addCell(cellCol0);
				wsheet2.addCell(cellCol1);
				wsheet2.addCell(cellCol2);
				wsheet2.addCell(cellCol3);
				wsheet2.addCell(cellCol4);
				wsheet2.addCell(cellCol5);
				wsheet2.addCell(cellCol6);
				wsheet2.addCell(cellCol7);
				wsheet2.addCell(cellCol8);
				wsheet2.addCell(cellCol9);
				wsheet2.addCell(cellCol10);
				startRow++;
			}	
			
			// 写入
			wwbook.write();
			// 关闭
			wwbook.close();
		} catch (Exception e) {			
			logger.error("创建Excel报表模板失败！", e);
		} 		
		return os;
	}
	
	/** 查询: 2.7.传统pos商户交易偏离值报表 */
	public ByteArrayOutputStream createPostradedeviant(List<Postradedeviant> source, String title, String startDate1, String endDate1,
			String startDate2, String endDate2,double change){
		// -------------------构造 可写入数据的EXCEL 结构    ------------------- //
		WritableWorkbook wwbook = null;	
		// 初始化一个20KB的内存空间
		ByteArrayOutputStream os = new ByteArrayOutputStream(20*1024);
		try {
			// 创建Excel
			wwbook = Workbook.createWorkbook(os);
			// 创建工作表sheet
			WritableSheet wsheet = wwbook.createSheet("附件1", 0);
						
			// 合并大标题单元格
			wsheet.mergeCells(0, 0, 10, 0);
			// 居中对齐
			titleFmt.setAlignment(Alignment.CENTRE);			
			jxl.write.Label c00 = new Label(0, 0, title,titleFmt);						
			jxl.write.Label c01 = new Label(0, 1, "报表生成日期：" + DateHelp.dateTime());
			jxl.write.Label c02 = new Label(0, 2, 
					"报表生成条件："	+DateHelp.numberToDate(startDate1)
					+"-"+DateHelp.numberToDate(endDate1)
					+"比" + DateHelp.numberToDate(startDate2) 
					+"-" + DateHelp.numberToDate(endDate2)
					+(change>0?"增加":"减少") + "大于" + Math.abs(change) + "% 的商户");			
			jxl.write.Label c03 = new Label(0, 3, "序号");
			jxl.write.Label c13 = new Label(1, 3, "商户编号");
			jxl.write.Label c23 = new Label(2, 3, "商户名称");
			jxl.write.Label c33 = new Label(3, 3, "终端号");
			jxl.write.Label c43 = new Label(4, 3, "地区");
			jxl.write.Label c53 = new Label(5, 3, DateHelp.numberToDate(startDate1)	+ "-" + DateHelp.numberToDate(endDate1) + "的交易额");			
			jxl.write.Label c63 = new Label(6, 3, DateHelp.numberToDate(startDate2)	+ "-" + DateHelp.numberToDate(endDate2) + "的交易额");
			jxl.write.Label c73 = new Label(7, 3, "实际增加/减少百分比");
			
			wsheet.addCell(c00);
			wsheet.addCell(c01);
			wsheet.addCell(c02);
			wsheet.addCell(c03);			
			wsheet.addCell(c13);
			wsheet.addCell(c23);
			wsheet.addCell(c33);
			wsheet.addCell(c43);
			wsheet.addCell(c53);			
			wsheet.addCell(c63);
			wsheet.addCell(c73);
						
			// -------------------从数据源中读取数据填充单元格    ------------------- //
			int startRow = 4; // 从第5行开
			for (int i=0; i<source.size(); i++) {
				Postradedeviant trade = source.get(i);
				jxl.write.Label cellCol0 = new Label(0, startRow, String.valueOf(i + 1));
				jxl.write.Label cellCol1 = new Label(1, startRow, trade.getMerchno());
				jxl.write.Label cellCol2 = new Label(2, startRow, trade.getMerchname());
				jxl.write.Label cellCol3 = new Label(3, startRow, trade.getTermno());
				jxl.write.Label cellCol4 = new Label(4, startRow, trade.getArea());				
				jxl.write.Label cellCol5 = new Label(5, startRow, trade.getTransmoney1());
				jxl.write.Label cellCol6 = new Label(6, startRow, trade.getTransmoney2());
				jxl.write.Label cellCol7 = new Label(7, startRow, trade.getChangeratio());
				
				wsheet.addCell(cellCol0);
				wsheet.addCell(cellCol1);
				wsheet.addCell(cellCol2);
				wsheet.addCell(cellCol3);
				wsheet.addCell(cellCol4);
				wsheet.addCell(cellCol5);
				wsheet.addCell(cellCol6);
				wsheet.addCell(cellCol7);
				startRow++;
			}			
			// 写入
			wwbook.write();
			// 关闭
			wwbook.close();
		} catch (Exception e) {			
			logger.error("创建Excel报表模板失败！", e);
		} 		
		return os;
	}
	
	/** 2.8.传统pos商户交易预警汇总报表 */
	public ByteArrayOutputStream createPoscollectwarningTemplet(PoscollectwarningVo vo, String title, String startDate, String endDate){
		// -------------------构造 可写入数据的EXCEL 结构    ------------------- //
		WritableWorkbook wwbook = null;	
		// 初始化一个20KB的内存空间
		ByteArrayOutputStream os = new ByteArrayOutputStream(20*1024);
		try {
			// 创建Excel
			wwbook = Workbook.createWorkbook(os);
			// 创建工作表sheet
			WritableSheet wsheet = wwbook.createSheet("附件1", 0);
						
			// 合并大标题单元格
			wsheet.mergeCells(0, 0, 10, 0);
			// 根据附件格式，拼合其它单元格
			wsheet.mergeCells(0, 3, 0, 4);//序号
			wsheet.mergeCells(1, 3, 1, 4);//商户编号
			wsheet.mergeCells(2, 3, 2, 4);//商户名称
			wsheet.mergeCells(3, 3, 3, 4);//终端号
			wsheet.mergeCells(4, 3, 4, 4);//地区
			wsheet.mergeCells(5, 3, 6, 3);//非营业时间
			wsheet.mergeCells(7, 3, 8, 3);//大额交易
			wsheet.mergeCells(9, 3, 10, 3);//信用卡频繁交易（3000元以上）
			wsheet.mergeCells(11, 3, 12, 3);//信用卡大额交易（1万以上）
			wsheet.mergeCells(13, 3, 14, 3);//信用卡频繁交易（10元以上）
			wsheet.mergeCells(15, 3, 17, 3);//信用卡交易
			// 居中对齐
			titleFmt.setAlignment(Alignment.CENTRE);			
			jxl.write.Label c00 = new Label(0, 0, title,titleFmt);						
			jxl.write.Label c01 = new Label(0, 1, "报表生成日期：" + DateHelp.dateTime());
			jxl.write.Label c02 = new Label(0, 2, "报表生成条件：开始日期 "+startDate+"            结束日期 "+endDate);			
			jxl.write.Label c0_34 = new Label(0, 3, "序号");
			jxl.write.Label c1_34 = new Label(1, 3, "商户编号");
			jxl.write.Label c2_34 = new Label(2, 3, "商户名称");
			jxl.write.Label c3_34 = new Label(3, 3, "终端号");
			jxl.write.Label c4_34 = new Label(4, 3, "地区");
			
			jxl.write.Label c56_3 = new Label(5, 3, "非营业时间");
			jxl.write.Label c5_4 = new Label(5, 4, "总额");
			jxl.write.Label c6_4 = new Label(6, 4, "笔数");
			
			jxl.write.Label c78_3 = new Label(7, 3, "大额交易");
			jxl.write.Label c7_4 = new Label(7, 4, "总额");
			jxl.write.Label c8_4 = new Label(8, 4, "笔数");
			
			jxl.write.Label c910_3 = new Label(9, 3, "信用卡频繁交易（3000元以上）");
			jxl.write.Label c9_4 = new Label(9, 4, "总额");
			jxl.write.Label c10_4 = new Label(10, 4, "笔数");
			
			jxl.write.Label c1112_3 = new Label(11, 3, "信用卡大额交易（1万以上）");
			jxl.write.Label c11_4 = new Label(11, 4, "总额");
			jxl.write.Label c12_4 = new Label(12, 4, "笔数");
			
			jxl.write.Label c1314_3 = new Label(13, 3, "信用卡频繁交易（10元以上）");
			jxl.write.Label c13_4 = new Label(13, 4, "总额");
			jxl.write.Label c14_4 = new Label(14, 4, "笔数");
			
			jxl.write.Label c151617_3 = new Label(15, 3, "信用卡交易");
			jxl.write.Label c15_4 = new Label(15, 4, "信用卡总额");
			jxl.write.Label c16_4 = new Label(16, 4, "笔数");
			jxl.write.Label c17_4 = new Label(17, 4, "占总交易额比例");
			
			wsheet.addCell(c00);
			wsheet.addCell(c01);
			wsheet.addCell(c02);
			wsheet.addCell(c0_34);			
			wsheet.addCell(c1_34);
			wsheet.addCell(c2_34);
			wsheet.addCell(c3_34);
			wsheet.addCell(c4_34);
			wsheet.addCell(c56_3);			
			wsheet.addCell(c5_4);
			wsheet.addCell(c6_4);
			wsheet.addCell(c78_3);
			wsheet.addCell(c7_4);
			wsheet.addCell(c8_4);
			wsheet.addCell(c910_3);
			wsheet.addCell(c9_4);
			wsheet.addCell(c10_4);
			wsheet.addCell(c1112_3);
			wsheet.addCell(c11_4);
			wsheet.addCell(c12_4);
			wsheet.addCell(c1314_3);
			wsheet.addCell(c13_4);
			wsheet.addCell(c14_4);
			wsheet.addCell(c151617_3);
			wsheet.addCell(c15_4);
			wsheet.addCell(c16_4);
			wsheet.addCell(c17_4);
			
						
			// -------------------从数据源中读取数据填充单元格    ------------------- //
			int startRow = 5; // 由于合并了一行，从第6行开
			List<Poscollectwarning> source = vo.poscollectwarnings;
			for (int i=0; i<source.size(); i++) {
				Poscollectwarning trade = source.get(i);
				jxl.write.Label cellCol0 = new Label(0, startRow, String.valueOf(i + 1));
				jxl.write.Label cellCol1 = new Label(1, startRow, trade.getMerchno());
				jxl.write.Label cellCol2 = new Label(2, startRow, trade.getMerchname());
				jxl.write.Label cellCol3 = new Label(3, startRow, trade.getTermno());
				jxl.write.Label cellCol4 = new Label(4, startRow, trade.getArea());
				
				// 非营业时间
				jxl.write.Label cell5_4 = new Label(5, startRow, trade.getNotbusetimeMoney());
				jxl.write.Label cell6_4 = new Label(6, startRow, trade.getNotbusetimeCount());
				
				// 大额交易
				jxl.write.Label cell7_4 = new Label(7, startRow, trade.getPosriskctrlMoney());
				jxl.write.Label cell8_4 = new Label(8, startRow, trade.getPosriskctrlCount());
				
				// 信用卡频繁交易（3000元以上）
				jxl.write.Label cell9_4 = new Label(9, startRow, trade.getPoscreditoftentimeMoney3000());
				jxl.write.Label cell10_4 = new Label(10, startRow, trade.getPoscreditoftentimeCount3000());
				
				// 信用卡大额交易（1万以上）
				jxl.write.Label cell11_4 = new Label(11, startRow, trade.getPoscreditriskctrlMoney());
				jxl.write.Label cell12_4 = new Label(12, startRow, trade.getPoscreditriskctrlCount());
				
				// 信用卡频繁交易（10元以上）
				jxl.write.Label cell13_4 = new Label(13, startRow, trade.getPoscreditoftentimeMoney10());
				jxl.write.Label cell14_4 = new Label(14, startRow, trade.getPoscreditoftentimeCount10());
				
				// 信用卡交易
				jxl.write.Label cell15_4 = new Label(15, startRow, trade.getPoscreditratioMoney());
				jxl.write.Label cell16_4 = new Label(16, startRow, trade.getPoscreditratioCount());
				jxl.write.Label cell17_4 = new Label(17, startRow, trade.getPoscreditratio());

				wsheet.addCell(cellCol0);
				wsheet.addCell(cellCol1);
				wsheet.addCell(cellCol2);
				wsheet.addCell(cellCol3);
				wsheet.addCell(cellCol4);
				
				wsheet.addCell(cell5_4);
				wsheet.addCell(cell6_4);				
				wsheet.addCell(cell7_4);
				wsheet.addCell(cell8_4);
				wsheet.addCell(cell9_4);				
				wsheet.addCell(cell10_4);
				wsheet.addCell(cell11_4);
				wsheet.addCell(cell12_4);				
				wsheet.addCell(cell13_4);
				wsheet.addCell(cell14_4);
				wsheet.addCell(cell15_4);				
				wsheet.addCell(cell16_4);
				wsheet.addCell(cell17_4);
				
				startRow++;
			}	
			// 写放合计
			startRow++;
			jxl.write.Label cell1Total4 = new Label(4, startRow, "合计:");
			
			jxl.write.Label cell1Total5 = new Label(5, startRow, vo.totalNotbusetimeMoney);
			jxl.write.Label cell1Total6 = new Label(6, startRow, vo.totalNotbusetimeCount);
			
			jxl.write.Label cell1Total7 = new Label(7, startRow, vo.totalPosriskctrlMoney);
			jxl.write.Label cell1Total8 = new Label(8, startRow, vo.totalPosriskctrlCount);
			
			jxl.write.Label cell1Total9 = new Label(9, startRow, vo.totalPoscreditoftentimeMoney3000);
			jxl.write.Label cell1Total10 = new Label(10, startRow, vo.totalPoscreditoftentimeCount3000);
			
			jxl.write.Label cell1Total11 = new Label(11, startRow, vo.totalPoscreditratioMoney);
			jxl.write.Label cell1Total12 = new Label(12, startRow, vo.totalPoscreditriskctrlCount);
			
			jxl.write.Label cell1Total13 = new Label(13, startRow, vo.totalPoscreditoftentimeMoney10);
			jxl.write.Label cell1Total14 = new Label(14, startRow, vo.totalPoscreditoftentimeCount10);
			
			jxl.write.Label cell1Total15 = new Label(15, startRow, vo.totalPoscreditratioMoney);
			jxl.write.Label cell1Total16 = new Label(16, startRow, vo.totalPoscreditratioCount);
			
			wsheet.addCell(cell1Total4);
			wsheet.addCell(cell1Total5);
			wsheet.addCell(cell1Total6);
			wsheet.addCell(cell1Total7);
			wsheet.addCell(cell1Total8);
			wsheet.addCell(cell1Total9);
			wsheet.addCell(cell1Total10);
			wsheet.addCell(cell1Total11);
			wsheet.addCell(cell1Total12);
			wsheet.addCell(cell1Total13);
			wsheet.addCell(cell1Total14);
			wsheet.addCell(cell1Total15);
			wsheet.addCell(cell1Total16);
			
			// 写入
			wwbook.write();
			// 关闭
			wwbook.close();
		} catch (Exception e) {			
			logger.error("创建Excel报表模板失败！", e);
		} 		
		return os;
	}
	
	//[end]
	
	
	/** 商户清算资金限额报表  */
	public ByteArrayOutputStream createLimitFreezeTemplet(List<LimitFreezeinfo> source, String title){
		// -------------------构造 可写入数据的EXCEL 结构    ------------------- //
		WritableWorkbook wwbook = null;	
		// 初始化一个20KB的内存空间
		ByteArrayOutputStream os = new ByteArrayOutputStream(20*1024);
		try {
			// 创建Excel
			wwbook = Workbook.createWorkbook(os);
			// 创建工作表sheet
			WritableSheet wsheet = wwbook.createSheet("附件1", 0);
						
			// 合并大标题单元格
			wsheet.mergeCells(0, 0, 15, 0);
			// 居中对齐
			titleFmt.setAlignment(Alignment.CENTRE);			
			jxl.write.Label c00 = new Label(0, 0, title,titleFmt);						
			jxl.write.Label c01 = new Label(0, 1, "报表生成日期：" + DateHelp.dateTime());
			jxl.write.Label c02 = new Label(0, 2, "");			
			jxl.write.Label c03 = new Label(0, 3, "序号");
			jxl.write.Label c13 = new Label(1, 3, "汇卡商户号");
			jxl.write.Label c23 = new Label(2, 3, "商户名称");			
			jxl.write.Label c33 = new Label(3, 3, "日应清算金额（元）");
			jxl.write.Label c123 = new Label(4, 3, "日限额（元）");	
			jxl.write.Label c53 = new Label(5, 3, "日划账金额（元）");
			jxl.write.Label c43 = new Label(6, 3, "日限额冻结金额（元）");								
			jxl.write.Label c63 = new Label(7, 3, "月清算金额（元）");
			jxl.write.Label c143 = new Label(8, 3, "月限额（元）");
			jxl.write.Label c73 = new Label(9, 3, "月限额冻结金额（元）");
			jxl.write.Label c83 = new Label(10, 3, "解冻金额（元）");			
			jxl.write.Label c93 = new Label(11, 3, "冻结时间");
			jxl.write.Label c103 = new Label(12, 3, "清算日期");
			jxl.write.Label c113 = new Label(13, 3, "清算天数");								
			jxl.write.Label c133 = new Label(14, 3, "日限额类型");		
			jxl.write.Label c153 = new Label(15, 3, "月限额类型");
																
			wsheet.addCell(c00);
			wsheet.addCell(c01);
			wsheet.addCell(c02);
			wsheet.addCell(c03);			
			wsheet.addCell(c13);
			wsheet.addCell(c23);
			wsheet.addCell(c33);
			wsheet.addCell(c43);
			wsheet.addCell(c53);
			wsheet.addCell(c63);
			wsheet.addCell(c73);
			wsheet.addCell(c83);
			wsheet.addCell(c93);
			wsheet.addCell(c103);
			wsheet.addCell(c113);
			wsheet.addCell(c123);
			wsheet.addCell(c133);
			wsheet.addCell(c143);
			wsheet.addCell(c153);
									
			// -------------------从数据源中读取数据填充单元格    ------------------- //
			int startRow = 4; // 从第5行开
			for (int i=0; i<source.size(); i++) {
				LimitFreezeinfo info = source.get(i);
				jxl.write.Label cellCol0 = new Label(0, startRow, String.valueOf(i + 1));
				jxl.write.Label cellCol1 = new Label(1, startRow, info.getWpmerchno());
				jxl.write.Label cellCol2 = new Label(2, startRow, info.getMerchname());
				jxl.write.Label cellCol3 = new Label(3, startRow, info.getDayamount());
				jxl.write.Label cellCol12 = new Label(4, startRow, info.getDaylimit());
				jxl.write.Label cellCol5 = new Label(5, startRow, info.getRealdayclear());
				jxl.write.Label cellCol4 = new Label(6, startRow, info.getDayfreezeamount());											
				jxl.write.Label cellCol6 = new Label(7, startRow, info.getMonthamount());
				jxl.write.Label cellCol14 = new Label(8, startRow, info.getMonthlimit());
				jxl.write.Label cellCol7 = new Label(9, startRow, info.getMonthfreezeamount());
				jxl.write.Label cellCol8 = new Label(10, startRow, info.getUnfreeze());				
				jxl.write.Label cellCol9 = new Label(11, startRow, info.getFreezetime());
				jxl.write.Label cellCol10 = new Label(12, startRow, info.getCleardate());
				jxl.write.Label cellCol11 = new Label(13, startRow, String.valueOf(info.getDaynum()));								
				jxl.write.Label cellCol13 = new Label(14, startRow, info.getDaylimittype());								
				jxl.write.Label cellCol15 = new Label(15, startRow, info.getMonthlimittype());
											
				wsheet.addCell(cellCol0);
				wsheet.addCell(cellCol1);
				wsheet.addCell(cellCol2);
				wsheet.addCell(cellCol3);
				wsheet.addCell(cellCol4);
				wsheet.addCell(cellCol5);
				wsheet.addCell(cellCol6);
				wsheet.addCell(cellCol7);
				wsheet.addCell(cellCol8);
				wsheet.addCell(cellCol9);
				wsheet.addCell(cellCol10);
				wsheet.addCell(cellCol11);
				wsheet.addCell(cellCol12);
				wsheet.addCell(cellCol13);
				wsheet.addCell(cellCol14);
				wsheet.addCell(cellCol15);
						
				startRow++;
			}			
			// 写入
			wwbook.write();
			// 关闭
			wwbook.close();
		} catch (Exception e) {
			logger.error("创建Excel报表模板失败！", e);
		} 		
		return os;
	}
	/**
	 * 商户异常统计报表（前置交易表）
	 * @param source
	 * @param title
	 * @param transDate
	 * @return
	 */
	public ByteArrayOutputStream createExceptionStatisticsTemplet(List<Map<String, Object>> source, String title, String transDate){
		// -------------------构造 可写入数据的EXCEL 结构    ------------------- //
		WritableWorkbook wwbook = null;	
		// 初始化一个20KB的内存空间
		ByteArrayOutputStream os = new ByteArrayOutputStream(20*1024);
		try {
			// 创建Excel
			wwbook = Workbook.createWorkbook(os);
			// 创建工作表sheet
			WritableSheet wsheet = wwbook.createSheet("附件1", 0);
						
			// 合并大标题单元格
			wsheet.mergeCells(0, 0, 10, 0);
			// 居中对齐
			titleFmt.setAlignment(Alignment.CENTRE);			
			jxl.write.Label c00 = new Label(0, 0, title,titleFmt);						
			jxl.write.Label c01 = new Label(0, 1, "报表生成日期：" + DateHelp.dateTime());
			jxl.write.Label c02 = new Label(0, 2, "报表生成条件：交易日期 "+transDate);			
			jxl.write.Label c03 = new Label(0, 3, "序号");
			jxl.write.Label c13 = new Label(1, 3, "渠道");
			jxl.write.Label c23 = new Label(2, 3, "商户号");
			jxl.write.Label c33 = new Label(3, 3, "终端号");
			jxl.write.Label c43 = new Label(4, 3, "总次数");
			jxl.write.Label c53 = new Label(5, 3, "失败次数");
			jxl.write.Label c63 = new Label(6, 3, "失败率");
			
			wsheet.addCell(c00);
			wsheet.addCell(c01);
			wsheet.addCell(c02);
			wsheet.addCell(c03);			
			wsheet.addCell(c13);
			wsheet.addCell(c23);
			wsheet.addCell(c33);
			wsheet.addCell(c43);
			wsheet.addCell(c53);
			wsheet.addCell(c63);
			
			String sortNo = "";
			String channelDesc = "";
			String mid = "";
			String termid = "";
			String allCount = "";
			String failCount = "";
			String failRate = "";
			// -------------------从数据源中读取数据填充单元格    ------------------- //
			int startRow = 4; // 从第5行开
			for (int i=0; i<source.size(); i++) {
				sortNo = source.get(i).get("sortNo") == null ? "" : source.get(i).get("sortNo").toString();
				channelDesc = source.get(i).get("channelDesc") == null ? "" : source.get(i).get("channelDesc").toString();
				mid = source.get(i).get("mid") == null ? "" : source.get(i).get("mid").toString();
				termid = source.get(i).get("termid") == null ? "" : source.get(i).get("termid").toString();
				allCount = source.get(i).get("allCount") == null ? "" : source.get(i).get("allCount").toString();
				failCount  = source .get(i).get("failCount") == null ? "" : source.get(i).get("failCount").toString();
				failRate  = source .get(i).get("failRate") == null ? "" : source.get(i).get("failRate").toString();
				
				jxl.write.Label cellCol0 = new Label(0, startRow, sortNo);
				jxl.write.Label cellCol1 = new Label(1, startRow, channelDesc);
				jxl.write.Label cellCol2 = new Label(2, startRow, mid);
				jxl.write.Label cellCol3 = new Label(3, startRow, termid);
				jxl.write.Label cellCol4 = new Label(4, startRow, allCount);
				jxl.write.Label cellCol5 = new Label(5, startRow, failCount);
				jxl.write.Label cellCol6 = new Label(6, startRow, failRate);
				wsheet.addCell(cellCol0);
				wsheet.addCell(cellCol1);
				wsheet.addCell(cellCol2);
				wsheet.addCell(cellCol3);
				wsheet.addCell(cellCol4);
				wsheet.addCell(cellCol5);
				wsheet.addCell(cellCol6);
				startRow++;
				sortNo = "";
				channelDesc = "";
				mid = "";
				termid = "";
				allCount = "";
				failCount = "";
				failRate = "";
			}			
			// 写入
			wwbook.write();
			// 关闭
			wwbook.close();
		} catch (Exception e) {
			logger.error("创建Excel报表模板失败！", e);
		} 		
		return os;
	}
}

